//package com.czn.datasourcesync.service;
//
//import com.czn.datasourcesync.model.SourceDataSource;
//import com.czn.datasourcesync.model.TargetDataSource;
//import org.slf4j.Logger;
//import org.slf4j.LoggerFactory;
//import org.springframework.beans.factory.annotation.Autowired;
//import org.springframework.beans.factory.annotation.Value;
//import org.springframework.scheduling.annotation.Scheduled;
//import org.springframework.stereotype.Service;
//
//import java.sql.*;
//
//
///**
// * @author Zenan Chen
// * @date 2023/9/15 11:23
// */
//@Service
//public class TempServiceImpl implements SyncDataSourceService {
//
//    private static final Logger log = LoggerFactory.getLogger(com.czn.datasourcesync.service.SyncDataSourceServiceImpl.class);
//    @Autowired
//    private SourceDataSource sourceDataSource;
//    @Autowired
//    private TargetDataSource targetDataSource;
//
//    @Value("${insert.batch.num:10000}")
//    private Integer size;
//
//
//    @Scheduled(cron = "${sync.cron}")
//    @Override
//    public void sync() {
//        long startTime = System.currentTimeMillis();
//        String sourceURL = sourceDataSource.getUrl();
//        String sourceUser = sourceDataSource.getUser();
//        String sourcePassword = sourceDataSource.getPassword();
//
//        String targetURL = targetDataSource.getUrl();
//        String targetUser = targetDataSource.getUser();
//        String targetPassword = targetDataSource.getPassword();
//
//        try (Connection conn1 = DriverManager.getConnection(sourceURL, sourceUser, sourcePassword); Connection conn2 = DriverManager.getConnection(targetURL, targetUser, targetPassword); ResultSet tables = conn1.getMetaData().getTables(null, null, null, new String[]{"TABLE"})) {
//            try (Statement stmt1 = conn1.createStatement(); Statement stmt2 = conn2.createStatement()) {
//                while (tables.next()) {
//                    String tableName = tables.getString("TABLE_NAME");
//                    log.info("==============================================【" + tableName + "】表开始 =========================================");
//                    // 获取源数据表的建表语句
//                    String createTableSQL = "show create table " + tableName;
//                    log.info("---------------> 在数据源查询【{}】的建表语句：{}", tableName, createTableSQL);
//                    ResultSet createTblRS = stmt1.executeQuery(createTableSQL);
//                    while (createTblRS.next()) {
//                        createTableSQL = createTblRS.getString(2);
//                        log.info("---------------> 在数据源查询【{}】的建表语句：\n{}", tableName, createTableSQL);
//                        // 查询目标表是否存在
//                        ResultSet tableExistRS = conn2.getMetaData().getTables(null, null, tableName, new String[]{"TABLE"});
//                        boolean exist = tableExistRS.next();
//                        if (exist) {
//                            log.info("---------------> 目标表【{}】存在！", tableName);
//                            // 删除目标表
//                            String dropTableSQL = "drop table " + tableName;
//                            log.info("---------------> 在目标源删除【{}】的建表语句：{}", tableName, dropTableSQL);
//                            stmt2.execute(dropTableSQL);
//                        }
//                        // 新建目标表
//                        stmt2.execute(createTableSQL);
//                        log.info("---------------> 在目标源执行【{}】的建表语句：\n{}", tableName, createTableSQL);
//                    }
//
//                    // 查询源表总记录数
//                    String queryCountSQL = "select count(1) from " + tableName;
//                    ResultSet queryCountRS = stmt1.executeQuery(queryCountSQL);
//                    int rows = 0;
//                    while (queryCountRS.next()) {
//                        rows = queryCountRS.getInt(1);
//                        log.info("---------------> 源表表【{}】的记录数是：", rows);
//                    }
//                    ResultSet queryRS = stmt1.executeQuery("SELECT * FROM " + tableName + ";");
//                    int columnCount = queryRS.getMetaData().getColumnCount();
//                    // 拼接INSERT语句
//                    StringBuilder sb = new StringBuilder("INSERT INTO " + tableName + " VALUES(");
//                    for (int j = 0; j < columnCount; j++) {
//                        sb.append("?,");
//                    }
//                    String insertSQL = sb.substring(0, sb.length() - 1);
//                    insertSQL += ");";
//                    log.info("---------------> insertSQL的结果是：{}", insertSQL);
//
//                    // 执行INSERT到目标表
//                    PreparedStatement prepareStatement = conn2.prepareStatement(insertSQL);
//
//                    // 分批查询源数据表
//                    int batch = (rows / size) + 1;
//                    log.info("---------------> 共：{}条记录，分{}批次进行执行，每批最大是{}", rows, batch, size);
//                    for (int i = 1; i <= batch; i++) {
//                        Integer start = (i - 1) * size;
//                        Integer end = size;
//                        ResultSet rs = stmt1.executeQuery("SELECT * FROM " + tableName + " limit " + start + "," + end);
//                        while (rs.next()) {
//                            for (int j = 1; j <= columnCount; j++) {
//                                prepareStatement.setObject(j, rs.getObject(j));
//                            }
//                            prepareStatement.addBatch();
//                        }
//                        prepareStatement.executeBatch();
//                    }
//                    log.info("==============================================【{}】表结束 =========================================", tableName);
//                }
//                log.info("Data copied.");
//                long endTime = System.currentTimeMillis();
//                long duration = endTime - startTime;
//                log.info("本次同步时间：{}", duration);
//            } catch (Exception e) {
//                log.error("SQLException: {}", e);
//            }
//        } catch (Exception e) {
//            log.error("SQLException: {}", e);
//        }
//    }
//}
//